#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
清空S7变量配置表数据
用于重新导入前清理现有数据
"""

import pymysql
import logging
import sys

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('s7_clear.log', encoding='utf-8'),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger(__name__)

def clear_s7_table():
    """清空s7_variables_config表数据"""
    
    # 数据库连接配置
    db_config = {
        'host': 'ldkjmysql.rwlb.rds.aliyuncs.com',
        'user': 'jiarelu',
        'password': 'jiareluLDKJ123!',
        'database': 'jrl_lab',
        'charset': 'utf8mb4'
    }
    
    try:
        # 连接数据库
        connection = pymysql.connect(**db_config)
        logger.info("数据库连接成功")
        
        cursor = connection.cursor()
        
        # 首先查询现有数据条数
        count_sql = "SELECT COUNT(*) FROM s7_variables_config"
        cursor.execute(count_sql)
        current_count = cursor.fetchone()[0]
        logger.info(f"当前表中有 {current_count} 条数据")
        
        if current_count > 0:
            # 用户确认
            confirm = input(f"确认要删除表中的 {current_count} 条数据吗？(y/N): ")
            if confirm.lower() in ['y', 'yes']:
                # 清空表数据
                clear_sql = "DELETE FROM s7_variables_config"
                cursor.execute(clear_sql)
                connection.commit()
                
                logger.info(f"已成功删除 {current_count} 条数据")
                
                # 重置自增ID（可选）
                reset_sql = "ALTER TABLE s7_variables_config AUTO_INCREMENT = 1"
                cursor.execute(reset_sql)
                connection.commit()
                logger.info("已重置自增ID")
                
            else:
                logger.info("操作已取消")
        else:
            logger.info("表中没有数据，无需清空")
        
        cursor.close()
        connection.close()
        logger.info("数据库连接已关闭")
        
    except Exception as e:
        logger.error(f"清空表数据时出错: {e}")

if __name__ == "__main__":
    clear_s7_table()